MySQL SQL問題集 part３

前提条件

問１ テーブル作成

データ定義に沿って、テーブル及びレコードを作成してください。

1. データ型に関しては、入力されているデータを元に推測して選定してください
2. 主キーは必ず全てのテーブルに設定してください ※どの項目を主キーとするかは考えて設定してみてください
3. 主キー以外の制約に関しては問いません

問２ データ抽出

１で作成したテーブルデータを元に以下のSQLを組み立てましょう。 基本的に各設問毎に１回のSQLで実現可能な形となっています。 なるべく少ない回数の問い合わせで済むようにしてみてください。

2.1. 以下のデータを取得するSQLを作成しなさい。

1. 商品一覧と各在庫数を取得するSQL

2. 顧客情報とクレジットカード情報を表示するSQL ※ クレジットカードを登録していない顧客は、クレジット情報をNULLで表示させる ※ ホームページなどで表示できるよう、性別などのコード値は各種マスタから名称を取得するように

3. 入庫予定の商品一覧と予定日、入庫先倉庫を表示するSQL ※入庫予定は、入庫TBLの完了日が入っていないものを指す

2.2. 顧客ID:CU00000002(高橋)から受注の以下のような注文が入った

• 商品ID・ITM0000002を100個、ITM0000005を90個

• 銀行振り込みで受注日は2014/12/4 ※ このシステムでは、以下のような仕様でデータを登録することになっている

  • 出荷予定日は各商品の入庫が完了次第出荷
  • 到着日は未定の為、null
    • ステータスは受注完了
    • 金額、消費税は現マスタ情報を参照して割り出すこと
    • 配送料は一律で \1,836

  この場合、下記のような仕様で登録する必要があるが、それぞれどんなSQLが必要になるか。SQLを作成しなさい

  1. シーケンスでオーダーNOを採番

  2. 注文情報テーブルに基本情報のみ登録する。他の項目はNULL

  3. 商品の注文数と注文時の価格を登録

  4. 受注日で税率をチェックし、総額から消費税を計算、更新

  5. 注文数と商品の在庫を判定

     1. 在庫があれば、在庫を引当た後に発注手続きへ
     2. 在庫が無ければ、ステータスを入荷待にして入庫予定をチェックし、入庫数と在庫数を合わせて注文数に足りているか判定 a．入庫数＋在庫数が注文数に足りていれば入庫された日を出荷予定日に b．入庫数＋在庫数が注文数に足りて居なければnullのまま